﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

namespace ServiceThuVien
{
    public class SQLServerDelete
    {        
        SqlConnection cn;
        public SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
        
        private string GetConnection()
        {
            builder.ConnectionString = "server=RADO-PC\\SQLEXPRESS;user id=sa;" +
            "password= 123;initial catalog=QLTV";
            return builder.ConnectionString;
        }

        private void ConnectSQL(ref SqlConnection cn, string pName, ref SqlCommand cmd)
        {
            cn = new SqlConnection(GetConnection());
            string procName = pName;
            cmd = new SqlCommand();
            cmd.CommandText = procName;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = cn;
        }
  
        #region Delete
        
        //Delete Bao Cao Muon Sach
        public int DeleteBCMuonSach(string MaBCMS)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "DeleteBCMuonSach";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaBCMS", MaBCMS);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Delete Bao Cao Tra Tre
        public int DeleteBCTraTre(int Ngay, string MaCuonSach)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "DeleteBCTraTre";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@Ngay", Ngay);
            cmd.Parameters.AddWithValue("@MaCuonSach", MaCuonSach);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Delete Chi Tiet Bao Cao Muon Sach
        public int DeleteCTBCMuonSach(string MaBCMS, string MaTheLoai)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "DeleteCTBCMuonSach";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaBCMS", MaBCMS);
            cmd.Parameters.AddWithValue("@MaTheLoai", MaTheLoai);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Delete Chi Tiet Phieu Muon
        public int DeleteCTPhieuMuon(string MaCuonSach, string MaPhieuMuon)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "DeleteCTPhieuMuon";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaCuonSach", MaCuonSach);
            cmd.Parameters.AddWithValue("@MaPhieuMuon", MaPhieuMuon);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Delete Chi Tiet Phieu Nhap
        public int DeleteCTPhieuNhap(string MaPhieuNhap, string MaSach)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "DeleteCTPhieuNhap";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaPhieuNhap", MaPhieuNhap);
            cmd.Parameters.AddWithValue("@MaSach", MaSach);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Delete Chi Tiet Phieu Tra
        public int DeleteCTPhieuTra(string MaPhieuTra, string MaCuonSach)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "DeleteCTPhieuTra";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaPhieuTra", MaPhieuTra);
            cmd.Parameters.AddWithValue("@MaCuonSach", MaCuonSach);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Delete Chi Tiet Tac Gia
        public int DeleteCTTacGia(string MaTacGia, string MaDauSach)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "DeleteCTTacGia";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaTacGia", MaTacGia);
            cmd.Parameters.AddWithValue("@MaDauSach", MaDauSach);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Delete Cuon Sach
        public int DeleteCuonSach(string MaCuonSach)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "DeleteCuonSach";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaCuonSach", MaCuonSach);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Delete Cuon Sach
        public int DeleteDauSach(string MaDauSach)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "DeleteDauSach";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaDauSach", MaDauSach);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Delete Cuon Sach
        public int DeleteDocGia(string MaDocGia)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "DeleteDocGia";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaDocGia", MaDocGia);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Delete Loai Doc Gia
        public int DeleteLoaiDocGia(string MaLoaiDocGia)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "DeleteLoaiDocGia";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaLoaiDocGia", MaLoaiDocGia);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Delete Phieu Muon
        public int DeletePhieuMuon(string MaPhieuMuon)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "DeletePhieuMuon";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaPhieuMuon", MaPhieuMuon);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Delete Phieu Nhap
        public int DeletePhieuNhap(string MaPhieuNhap)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "DeletePhieuNhap";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaPhieuNhap", MaPhieuNhap);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Delete Phieu Phat
        public int DeletePhieuPhat(string MaPhieuPhat)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "DeletePhieuPhat";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaPhieuPhat", MaPhieuPhat);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Delete Phieu Phat
        public int DeletePhieuTra(string MaPhieuTra)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "DeletePhieuTra";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaPhieuTra", MaPhieuTra);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Delete Sach
        public int DeleteSach(string MaSach)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "DeleteSach";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaSach", MaSach);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Delete Tac Gia
        public int DeleteTacGia(string MaTacGia)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "DeleteTacGia";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaTacGia", MaTacGia);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Delete The Loai
        public int DeleteTheLoai(string MaTheLoai)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "DeleteTheLoai";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaTheLoai", MaTheLoai);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        //Delete Nhan Vien
        public int DeleteNhanVien(string MaNhanVien)
        {
            cn = new SqlConnection(GetConnection());

            string procName = "DeleteNhanVien";
            SqlCommand cmd = new SqlCommand();

            ConnectSQL(ref cn, procName, ref cmd);

            cmd.Parameters.AddWithValue("@MaNhanVien", MaNhanVien);

            try
            {
                cn.Open();
                int num = cmd.ExecuteNonQuery();
                cn.Close();
                return num;
            }
            catch
            {
                return 0;
            }
        }

        #endregion

    }
}